<?php
/**
 * @description : Model for Report pages.
 * @class-name : ModelReportPages()
 * @author : stephen.mo
 * @date : 2010-04-22
 */

class ModelReportPages extends model {
    
    static public function getTableName() {
    }

    //set the table name.
    static public function getTableNameOfVideoAccessLog($year_month=null) {
        $year_month = (empty($year_month)) ? date('Y_m') : $year_month;
        return self::getTablePrefixNameOfVideoAccessLog().$year_month;
    }
    
    //define Prefix of table name.
    static private function getTablePrefixNameOfVideoAccessLog() {
        return 'video_access_log_';
    }
    
    //define table name of subscription.
    static private function getTableNameOfSubscription() {
        return 'subscription_log';
    }
    
    //define table name of sing-up
    static private function getTableNameOfSignUp() {
        return 'fp_page_access';
    }
    
    //define day table name of vide bitrate.
    static private function getDayTableNameOfVideoBitrate() {
        return 'video_bitrate_report_by_day';
    }
    
    //define day table name of video viewing.
    static private function getDayTableNameOfVideoViewing() {
        return 'video_viewing_report_by_day';
    }

    //define day table name of fp video access.
    static private function getDayTableNameOfOSAndBrowser() {
        return 'os_browser_report_by_day';
    }
    
    //define daily table of member access log.
    static private function getDailyMemberLog() {
        return 'daily_member_log';
    }

    //define the mstat inline table.
    static private function getMstatInlineMembership() {
        return 'mstat_inline_membership';
    }
    
    //define the hot search table.
    static private function getMstatInlineHotSearchDaily() {
        return 'daily_hot_search';
    }

    static private function getMstatInlineHotSearchList() {
        return 'hot_search_list';
    }

    //set table name with month.
    private function setTableName($table_name) {
        if (isset($table_name['month']) && $table_name['month'] != 0) {
            $year_month = $table_name['year'].'_'.$table_name['month'].' AS '.AC::$table_alias_name;
            $table_list = self::getTableNameOfVideoAccessLog($year_month);
        }
        else {
            $table_prefix = self::getTablePrefixNameOfVideoAccessLog();
            $table_list = '';
            foreach((array)$table_name['year'] as $key=>$value) {
                $tbl_name = $table_prefix.'merge_'.$value;
                $table_list .= $tbl_name.' AS Tbl_'.$key.' ,';
            }
            $table_list = substr($table_list,0,strlen($table_list)-1);
        }

        return $table_list;
    }

    //get top ten records by categories.
    public function getTopTenRecordByCategory($table_name,$condition,$sort,$category=array()) {
        $result = array();
        if (empty($table_name)) return $result;
        $tbl_alias = AC::$table_alias_name;

        foreach($category as $key=>$val) {
            $query = "SELECT $tbl_alias.video_id,$tbl_alias.video_title,count($tbl_alias.id) as frequency,sum($tbl_alias.duration) as duration ".
                     " FROM ".$this->setTableName($table_name).
                     " WHERE $tbl_alias.category_path='".$val."'".$condition.
                     " GROUP BY $tbl_alias.video_id ".
                     " ORDER BY ".$sort." DESC ".
                     " LIMIT 10 ";
            $data = Model::selectBySql($query);
            $result[$val] = $data;
        } 
        return $result;
    }
    
    //get top ten records.
    public function getTopTenRecords($table_name,$condition,$sort) {
        $result = array();
        if (empty($table_name)) return $result;
        $tbl_alias = AC::$table_alias_name;

        $query = "SELECT $tbl_alias.video_id,$tbl_alias.video_title,count($tbl_alias.id) as frequency,sum($tbl_alias.duration) as duration ".
                 " FROM ".$this->setTableName($table_name).
                 " WHERE 1=1 ".$condition.
                 " GROUP BY $tbl_alias.video_id ".
                 " ORDER BY ".$sort." DESC ".
                 " LIMIT 10 ";
        echo $query."<br>";
        //die();
        $data = Model::selectBySql($query);
        $result['None'] = $data;
        return $result;
    }
    
    //GenderAge
    public function getGenderAgeGroupCount($table_name,$age_group,$category_path,$gender,$condition,$sort_by,$video_id) {
        $result = array();
        if (empty($table_name)) return $result;
        $append_condition = null;
        $tbl_alias = AC::$table_alias_name;

        if (!empty($category_path) && strtolower($category_path) != 'none') {
            $append_condition = ' AND '.$tbl_alias.'.category_path="'.$category_path.'" ';
        }

        if (empty($gender)) {
            $query = "SELECT $tbl_alias.gender,$tbl_alias.age_group,count($tbl_alias.id) as frequency,sum($tbl_alias.duration) as duration".
                     " FROM ".$this->setTableName($table_name).
                     " WHERE $tbl_alias.video_id=".$video_id.$condition.$append_condition.
                     " GROUP BY $tbl_alias.age_group ".
                     " ORDER BY ".$sort_by." DESC ";
            
            $data = Model::selectBySql($query);
            $tmp_result = array();
            foreach($data as $k=>$v) {
                $age_group_id = $v['age_group'];
                $tmp_result[$age_group_id] = array('frequency'=>$v['frequency'],'duration'=>$v['duration']);
            }
            $result[0] = $tmp_result;
        }
        else {
            foreach($gender as $key=>$val) {
                $query = "SELECT $tbl_alias.gender,$tbl_alias.age_group,count($tbl_alias.id) as frequency,sum($tbl_alias.duration) as duration".
                         " FROM ".$this->setTableName($table_name).
                         " WHERE $tbl_alias.gender='".$val."' AND $tbl_alias.video_id=".$video_id.$condition.$append_condition.
                         " GROUP BY $tbl_alias.age_group ".
                         " ORDER BY ".$sort_by." DESC ";
                $data = Model::selectBySql($query);
                $tmp_result = array();
                foreach($data as $k=>$v) {
                    $age_group_id = $v["age_group"];
                    $tmp_result[$age_group_id] = array('frequency'=>$v['frequency'],'duration'=>$v['duration']);
                }
                $result[$val] = $tmp_result;
            }
        }

        return $result;
    }

    //SubscriptionPlan
    public function getSubscriptionPlanGroupCount($table_name,$package_id,$category_path,$content_type,$condition,$sort_by,$video_id) {
        $result = array();
        if (empty($table_name)) return $result;
        $append_condition = null;
        $tbl_alias = AC::$table_alias_name;
        $subscription_plan = AC::$video_subscription_alias;
        
        if (!empty($category_path) && strtolower($category_path) != 'none') {
            $append_condition = ' AND '.$tbl_alias.'.category_path="'.$category_path.'" ';
        }

        $query = "SELECT $tbl_alias.content_type,$tbl_alias.package_id,count($tbl_alias.id) as frequency,sum($tbl_alias.duration) as duration".
                 " FROM ".$this->setTableName($table_name).
                 " WHERE $tbl_alias.video_id=".$video_id.$condition.$append_condition.
                 " GROUP BY $tbl_alias.package_id ".
                 " ORDER BY ".$sort_by." DESC ";
        $data = Model::selectBySql($query);
        $tmp_result = array();
        foreach($data as $k=>$v) {
            $package_id = $v["package_id"];
            $tmp_result[$package_id] = array('frequency'=>$v['frequency'],'duration'=>$v['duration']);
        }
        $result[$subscription_plan[$content_type]] = $tmp_result;

        return $result;
    }
    
    //RegionCountry
    public function getRegionCountryGroupCount($table_name,$country_code,$category_path,$content_type,$condition,$sort_by,$video_id) {
        $result = array();
        if (empty($table_name)) return $result;
        $append_condition = null;
        $tbl_alias = AC::$table_alias_name;
        
        if (!empty($category_path) && strtolower($category_path) != 'none') {
            $append_condition = ' AND '.$tbl_alias.'.category_path="'.$category_path.'" ';
        }

        $query = "SELECT $tbl_alias.content_type,$tbl_alias.country_code,count($tbl_alias.id) as frequency,sum($tbl_alias.duration) as duration".
                 " FROM ".$this->setTableName($table_name).
                 " WHERE $tbl_alias.video_id=".$video_id.$condition.$append_condition.
                 " GROUP BY $tbl_alias.country_code ".
                 " ORDER BY ".$sort_by." DESC ";
        $data = Model::selectBySql($query);
        $tmp_result = array();
        foreach($data as $k=>$v) {
            $country_code_ = $v["country_code"];
            $tmp_result[$country_code_] = array('frequency'=>$v['frequency'],'duration'=>$v['duration']);
        }

        $tmp_country_code_key = array_keys($tmp_result);
        foreach($country_code as $key=>$value) {
            foreach($value as $key2=>$val2) {
                foreach($val2 as $key3=>$val3) {
                    if (in_array($key3,$tmp_country_code_key)) {
                        $result[$key][$key2][$key3] = $tmp_result[$key3];
                    }
                }
            }
        }
        
        return $result;        
    }

    //video bitrate
    public function videoBitrateOfCountry($table_name,$country_code,$condition) {
        $result = array();
        if (empty($table_name)) return $result;
        $append_condition = null;
        $tbl_alias = AC::$table_alias_name;
        $table_name = $this->setTableName($table_name);

        if (!empty($country_code)) {
            foreach($country_code as $key=>$value) {
                $tmp_country = array();
                $tmp_list_two = array();
                foreach($value as $key2=>$value2) {
                    foreach($value2 as $k=>$v) {
                        $tmp_list_one = array();
                        $append_condition = " AND $tbl_alias.country_code='".$k."' ";
                        $limit_num = $this->countMember($table_name,$tbl_alias,$append_condition);
                        if ($limit_num>0) {
                            $using_ids = $this->getIdsByCondition($table_name,$tbl_alias,$append_condition,$limit_num);
                            if (count($using_ids)>0) {                
                                $query = "SELECT $tbl_alias.bitrate,count($tbl_alias.member_id) as Total FROM ".$table_name.
                                         " WHERE $tbl_alias.id IN (".implode(',',$using_ids).") ".
                                         " GROUP BY $tbl_alias.bitrate ".
                                         " ORDER BY $tbl_alias.bitrate DESC ";
                                $data = Model::selectBySql($query);
                                foreach($data as $k2=>$v2) {
                                    $tmp_list_one[$v2['bitrate']] = $v2['Total'];
                                }
                            }
                        }
                        $tmp_list_two[$k] = $tmp_list_one;
                    }
                }

                $result[$key] = $tmp_list_two;
            }
        }
        else {
            $tmp_array = array();
            $limit_num = $this->countMember($table_name,$tbl_alias,null);
            if ($limit_num>0) {
                $using_ids = $this->getIdsByCondition($table_name,$tbl_alias,null,$limit_num);
                if (count($using_ids)>0) {
                    $query = "SELECT $tbl_alias.bitrate,count($tbl_alias.member_id) as Total FROM ".$table_name.
                             " WHERE $tbl_alias.id IN (".implode(',',$using_ids).") ".
                             " GROUP BY $tbl_alias.bitrate ".
                             " ORDER BY $tbl_alias.bitrate ASC ";
                    $data = Model::selectBySql($query);
                    foreach($data as $k2=>$v2) {
                        $tmp_array[$v2['bitrate']] = $v2['Total'];
                    }
                }
            }
            $result['summary'] = $tmp_array;
        }

        return $result;
    }

    //count all member nums
    private function countMember($table,$tbl_alias,$condition) {
        $query = "SELECT DISTINCT $tbl_alias.member_id FROM ".$table." WHERE $tbl_alias.live=0 ".$condition;
        $data = Model::selectBySql($query);
        return count($data);
    }

    //get the top ten video ids by condition.
    private function getIdsByCondition($table,$tbl_alias,$condition,$limit_num) {
        $query = "SELECT $tbl_alias.id FROM $table WHERE $tbl_alias.live=0 ".$condition." GROUP BY $tbl_alias.member_id DESC LIMIT ".$limit_num;
        $data = Model::selectBySql($query);
        $tmp = array();
        foreach($data as $k=>$v) {
            $tmp[] = $v['id'];
        }
        return $tmp;        
    }


    //subscription plans count & line chart
    //gender&age group for subscription plans.
    public function getGenderAndAgeGroupForPlan($condition,$package_ids,$dateby) {
        $result = array();
        $data = array();
        $append_condition = null;
        $table_name = self::getTableNameOfSubscription();

        $query = "SELECT order_datetime,gender,type,package_id ".
                 " FROM ".$table_name.
                 " WHERE plan_status=1 ".$condition.
                 " GROUP BY order_id ".
                 " ORDER BY order_datetime ASC ";
        $data = Model::selectBySql($query);
                
        foreach($data as $key=>$value) {
            $package_id = $value['package_id'];
            $gender = $value['gender'];
            $type = $value['type'];
            $date_time = ($dateby=='date') ? substr($value['order_datetime'],0,10) : substr($value['order_datetime'],0,7);
            if (!isset($result[$date_time][$package_id][$gender]['new'])) {
                $result[$date_time][$package_id][$gender]['new'] = 0;
            }
            if (!isset($result[$date_time][$package_id][$gender]['renew'])) {
                $result[$date_time][$package_id][$gender]['renew'] = 0;
            }
            //
            if ($type==1) {
                $result[$date_time][$package_id][$gender]['new']++;
            }
            elseif($type==2) {
                $result[$date_time][$package_id][$gender]['renew']++;
            }
        }

        return $result;
    }

    //region&country for subscription plans.
    public function getRegionAndCountryGroupForPlan($condition,$package_ids,$dateby) {
        $result = array();
        $data = array();
        $table_name = self::getTableNameOfSubscription();

        $query = "SELECT order_datetime,country_code,type,package_id ".
                 " FROM ".$table_name.
                 " WHERE plan_status=1 ".$condition.
                 " GROUP BY order_id,package_id,package_version ".
                 " ORDER BY order_datetime ASC ";
        $data = Model::selectBySql($query);

        foreach($data as $key=>$value) {
            //$tmp = array();
            $package_id = $value['package_id'];
            $country_code_ = $value['country_code'];
            $type = $value['type'];
            $date_time = ($dateby=='date') ? substr($value['order_datetime'],0,10) : substr($value['order_datetime'],0,7);
            if (!isset($result[$date_time][$package_id][$country_code_]['new'])) {
                $result[$date_time][$package_id][$country_code_]['new'] = 0;
            }
            if (!isset($result[$date_time][$package_id][$country_code_]['renew'])) {
                $result[$date_time][$package_id][$country_code_]['renew'] = 0;
            }
            if ($type==1) {
                $result[$date_time][$package_id][$country_code_]['new']++;
            }
            elseif($type==2) {
                $result[$date_time][$package_id][$country_code_]['renew']++;
            }
        }
        return $result;
    }
    
    //region&country for sign-up.
    public function getRegionAndCountryGroupForSignUp($condition) {
        $result = array();
        $data = array();
        $table_name = self::getTableNameOfSignUp();
        $query = "SELECT access_datetime,country_code,count(login_id) as total,page_type,step ".
                 " FROM ".$table_name.
                 " WHERE 1=1 ".$condition.
                 " GROUP BY page_type,country_code,step ".
                 " ORDER BY access_datetime ASC ";
        $data = Model::selectBySql($query);
        
        foreach($data as $key=>$value) {
            $country_code = $value['country_code'];
            $step_num = $value['step'];
            $page_type = $value['page_type'];
            $total = $value['total'];
            $result[$country_code][$page_type][$step_num] = $total;
        }

        return $result;
    }


    //Data Query Optimization
    //1.premuim video viewing report.
    #total summary.
    public function getTopTenRecordsOptimization($condition,$filter_condition,$sort,$top_limit) {
        //if the totoal summary needn't the condition ,will use this codes.
        $filter_condition = array('category_path'=>array(),
                                  'countries_code'=>array(),
                                  'content_type'=>NULL,
                                  'genders'=>array(),
                                  'pakcage_ids'=>array(),
                                  'age_groups'=>array());
        $data = array();
        $result = array();
        $table_name = $this->getDayTableNameOfVideoViewing();
        
        $query = "SELECT day_time,json_data ".
                 " FROM ".$table_name.
                 " WHERE 1 ".$condition.
                 " ORDER BY day_time ASC ";
        $data = Model::selectBySql($query);

        $tmp_data = array();
        $tmp_result = array();
        foreach($data as $value) {
            $json_data_arr = array();
            $json_data_arr = json_decode($value['json_data'],true);
            
            foreach($json_data_arr as $category_path=>$category_val) {

                if (!empty($filter_condition['category_path']) && !in_array($category_path,$filter_condition['category_path'])) continue;
                foreach($category_val as $video_id=>$video_val) {
                    foreach($video_val as $country_code=>$country_val) {
                        
                        if (!empty($filter_condition['countries_code']) && !in_array($country_code,$filter_condition['countries_code'])) continue;
                        foreach($country_val as $plan_id=>$plan_val) {
                            
                            if (isset($filter_condition['content_type']) && !empty($filter_condition['content_type'])) {
                                if ($filter_condition['content_type']==1 && $plan_id != 0) continue;
                                if ($filter_condition['content_type']==2 && !in_array($plan_id,$filter_condition['pakcage_ids'])) continue;
                            }
                            foreach($plan_val as $gender=>$gender_val) {
                                
                                if (!empty($filter_condition['genders']) && !in_array($gender,$filter_condition['genders'])) continue;
                                foreach($gender_val as $age_group=>$age_val) {
                                    
                                    if (!empty($filter_condition['age_groups']) && !in_array($age_group,$filter_condition['age_groups'])) continue;
                                    $data_frequency = (int)$age_val['frequency'];
                                    $data_duration = (int)$age_val['duration'];
                                    if (!isset($tmp_result[$video_id])) {
                                        $tmp_result[$video_id] = array('frequency'=>0,'duration'=>0,'video_id'=>null);
                                    }
                                    $tmp_result[$video_id]['frequency'] += $data_frequency;
                                    $tmp_result[$video_id]['duration'] += $data_duration;
                                    $tmp_result[$video_id]['video_id'] = $video_id;
                                }
                            }
                        }
                    }
                }
            }
            if (isset($json_data_arr)) unset($json_data_arr);
        }

        //
        if (empty($tmp_result)) return array('data'=>array(),'video_ids'=>array());        
        if (isset($data)) unset($data);
        $sort_name_desc1 = strtolower($sort);
        $sort_name_desc2 = (strtolower($sort)=='frequency') ? 'duration' : 'frequency';
        foreach($tmp_result as $key=>$value) {
            $desc_data_first[$key] = $value[$sort_name_desc1];
            $desc_data_second[$key] = $value[$sort_name_desc2];
        }
        //sort the data.
        $sort_flag = array_multisort($desc_data_first,SORT_DESC,$desc_data_second,SORT_DESC,$tmp_result);
        if (isset($desc_data_first)) unset($desc_data_first);
        if (isset($desc_data_second)) unset($desc_data_second);
        
        //if sort success then get top 10 and return
        $result['None'] = array();
        $video_ids = array();
        if ($sort_flag) {
            $n = 1;
            foreach($tmp_result as $value) {
                if ($n>$top_limit) break;
                $video_id = $value['video_id'];
                $result['None'][$video_id]['attr'] = array('frequency'=>$value['frequency'],'duration'=>$value['duration']);
                $video_ids[] = $video_id;
                $n++;
            }
        }
        
        if (isset($tmp_result)) unset($tmp_result);
        if (isset($tmp_data)) unset($tmp_data);
        return array('data'=>$result,'video_ids'=>$video_ids);
    }

    #get All datas with All Conditions.
    public function getAllGroupDataOptimization($condition,$category_paths,$age_groups,$genders,$content_types,$package_ids,$country_codes,$sort_by,$exist_group,$pegging_countries_code,$top_limit) {
        $data = array();
        $result = array();
        $tmp_result = array();
        if (empty($exist_group)) return $result;
        $table_name = $this->getDayTableNameOfVideoViewing();
        
        //create SQL string.
        $query = "SELECT day_time,json_data ".
                 " FROM ".$table_name.
                 " WHERE 1 ".$condition.
                 " ORDER BY day_time ASC ";
        $data = Model::selectBySql($query);
        
        //get group data by exist_group with a loop.
        foreach($exist_group as $group_val) {
            $method_name = 'get'.$group_val.'GroupCountOptimization';
            $tmp_result = $this->{$method_name}($data,$category_paths,$age_groups,$genders,$content_types,$package_ids,$country_codes,$sort_by,$pegging_countries_code,$top_limit);
            $result[$group_val] = $tmp_result;
        }

        return $result;
    }

    #age group & gender.
    public function getGenderAgeGroupCountOptimization($data,$category_paths,$age_groups,$genders,$content_types,$package_ids,$country_codes,$sort_by,$pegging_countries_code,$top_limit) {
        if (empty($data)) return array();
        $result = array();
        $tmp_data = array();
        $tmp_result = array();

        foreach($data as $value) {
            $json_data_arr = array();
            $json_data_arr = json_decode($value['json_data'],true);
            
            foreach($json_data_arr as $category_path=>$category_val) {
                //if no exist then unset it.
                if (!empty($category_paths) && !in_array(strtolower($category_path),$category_paths)) continue;
                if (!empty($category_paths) && !isset($tmp_data[$category_path])) $tmp_data[$category_path] = array();
                if (empty($category_paths) && !isset($tmp_data['None'])) $tmp_data['None'] = array();

                foreach($category_val as $video_id=>$video_val) {
                    //if no exist then unset it.
                    if (!empty($category_paths) && !isset($tmp_data[$category_path][$video_id])) {
                        $tmp_data[$category_path][$video_id] = array();
                        $tmp_data[$category_path][$video_id]['list'] = array();
                    }
                    elseif (empty($category_paths) && !isset($tmp_data['None'][$video_id])) {
                            $tmp_data['None'][$video_id] = array();
                            $tmp_data['None'][$video_id]['list'] = array();
                    }
                    
                    foreach($video_val as $country_code=>$country_val) {
                        //if no exist then unset it.
                        if (!empty($country_codes) && !in_array($country_code,$country_codes)) continue;

                        foreach($country_val as $plan_id=>$plan_val) {
                            //if no exist then unset it.
                            if ($content_types==2 && !empty($package_ids) && !in_array($plan_id,$package_ids)) continue;
                            if ($content_types==1 && $plan_id!=0) continue;

                            foreach($plan_val as $gender=>$gender_val) {
                                //if no exist then unset it.
                                if (!empty($genders) && !in_array($gender,$genders)) continue;

                                if (!empty($genders) && !empty($category_paths) && count($genders==2)) {
                                    if (!isset($tmp_data[$category_path][$video_id]['list']['M'])) $tmp_data[$category_path][$video_id]['list']['M'] = array();
                                    if (!isset($tmp_data[$category_path][$video_id]['list']['F'])) $tmp_data[$category_path][$video_id]['list']['F'] = array();
                                }
                                elseif(!empty($genders) && !empty($category_paths)) {
                                    $tmp_data[$category_path][$video_id]['list'][$gender] = array();
                                }
                                elseif (!empty($genders) && empty($category_paths) && count($genders)==2) {
                                    if (!isset($tmp_data['None'][$video_id]['list']['M'])) $tmp_data['None'][$video_id]['list']['M'] = array();
                                    if (!isset($tmp_data['None'][$video_id]['list']['F'])) $tmp_data['None'][$video_id]['list']['F'] = array();
                                }
                                elseif (!empty($genders) && empty($category_paths)) {
                                    if (!isset($tmp_data['None'][$video_id]['list'][$gender])) $tmp_data['None'][$video_id]['list'][$gender] = array();
                                }
                                elseif (empty($genders)) {
                                    if (!isset($tmp_data['None'][$video_id]['list'][0])) $tmp_data['None'][$video_id]['list'][0] = array();
                                    if (!isset($tmp_data[$category_path][$video_id]['list'][0])) $tmp_data[$category_path][$video_id]['list'][0] = array();
                                }

                                /**
                                if (!empty($genders)) {
                                    if (!empty($category_paths)) {
                                        if (count($genders)==2) {
                                            if (!isset($tmp_data[$category_path][$video_id]['list']['M'])) {
                                                $tmp_data[$category_path][$video_id]['list']['M'] = array();
                                            }
                                            if (!isset($tmp_data[$category_path][$video_id]['list']['F'])) {
                                                $tmp_data[$category_path][$video_id]['list']['F'] = array();
                                            }
                                        }
                                        else {
                                                $tmp_data[$category_path][$video_id]['list'][$gender] = array();
                                        }
                                    }
                                    else {
                                        if (count($genders)==2) {
                                            if (!isset($tmp_data['None'][$video_id]['list']['M'])) {
                                                $tmp_data['None'][$video_id]['list']['M'] = array();
                                            }
                                            if (!isset($tmp_data['None'][$video_id]['list']['F'])) {
                                                $tmp_data['None'][$video_id]['list']['F'] = array();
                                            }
                                        }
                                        else {
                                            $tmp_data['None'][$video_id]['list'][$gender] = array();
                                        }
                                    }
                                }
                                elseif(empty($genders)) {
                                    if (!isset($tmp_data['None'][$video_id]['list'][0])) {
                                        $tmp_data['None'][$video_id]['list'][0] = array();
                                    }
                                    if (!isset($tmp_data[$category_path][$video_id]['list'][0])) {
                                        $tmp_data[$category_path][$video_id]['list'][0] = array();
                                    }
                                }
                                */

				                foreach($gender_val as $age_group=>$age_group_val) {
                                    //if no exist then unset it.
                                    if (!empty($age_groups) && !in_array($age_group,$age_groups)) continue;

                                    if (!empty($category_paths)) {
                                        if (!empty($genders)) {
                                            if (!isset($tmp_data[$category_path][$video_id]['list'][$gender][$age_group])) {
                                                $tmp_data[$category_path][$video_id]['list'][$gender][$age_group] = array();
                                            }
                                        }
                                        else {
                                            if (!isset($tmp_data[$category_path][$video_id]['list'][0][$age_group])) {
                                                $tmp_data[$category_path][$video_id]['list'][0][$age_group] = array();
                                            }
                                        }
                                        if (isset($tmp_data['None'])) unset($tmp_data['None']);
                                    }
                                    else {
                                        if (!empty($genders)) {
                                            if (!isset($tmp_data['None'][$video_id]['list'][$gender][$age_group])) {
                                                $tmp_data['None'][$video_id]['list'][$gender][$age_group] = array();
                                            }
                                        }
                                        else {
                                            if (!isset($tmp_data['None'][$video_id]['list'][0][$age_group])) {
                                                $tmp_data['None'][$video_id]['list'][0][$age_group] = array();
                                            }
                                        }
                                    }

                                    //get data.
                                    $num_frequency = (int)$age_group_val['frequency'];
                                    $num_duration = (int)$age_group_val['duration'];
                                    

                                    //
                                    if (!empty($genders)) {
                                        if (!empty($category_paths)) {
                                            if (!isset($tmp_data[$category_path][$video_id]['list'][$gender][$age_group]['frequency'])) {
                                                $tmp_data[$category_path][$video_id]['list'][$gender][$age_group]['frequency'] = 0;
                                            }
                                            if (!isset($tmp_data[$category_path][$video_id]['list'][$gender][$age_group]['duration'])) {
                                                $tmp_data[$category_path][$video_id]['list'][$gender][$age_group]['duration'] = 0;
                                            }
                                            $tmp_data[$category_path][$video_id]['list'][$gender][$age_group]['frequency'] += $num_frequency;
                                            $tmp_data[$category_path][$video_id]['list'][$gender][$age_group]['duration'] += $num_duration;
                                        }
                                        else {
                                            if (!isset($tmp_data['None'][$video_id]['list'][$gender][$age_group]['frequency'])) {
                                                $tmp_data['None'][$video_id]['list'][$gender][$age_group]['frequency'] = 0;
                                            }
                                            if (!isset($tmp_data['None'][$video_id]['list'][$gender][$age_group]['duration'])) {
                                                $tmp_data['None'][$video_id]['list'][$gender][$age_group]['duration'] = 0;
                                            }
                                            $tmp_data['None'][$video_id]['list'][$gender][$age_group]['frequency'] += $num_frequency;
                                            $tmp_data['None'][$video_id]['list'][$gender][$age_group]['duration'] += $num_duration;
                                        }
                                    }
                                    else {
                                        if (!empty($category_paths)) {
                                            if (!isset($tmp_data[$category_path][$video_id]['list'][0][$age_group]['frequency'])) {
                                                $tmp_data[$category_path][$video_id]['list'][0][$age_group]['frequency'] = 0;
                                            }
                                            if (!isset($tmp_data[$category_path][$video_id]['list'][0][$age_group]['duration'])) {
                                                $tmp_data[$category_path][$video_id]['list'][0][$age_group]['duration'] = 0;
                                            }
                                            $tmp_data[$category_path][$video_id]['list'][0][$age_group]['frequency'] += $num_frequency;
                                            $tmp_data[$category_path][$video_id]['list'][0][$age_group]['duration'] += $num_duration;
                                        }
                                        else {
                                            if (!isset($tmp_data['None'][$video_id]['list'][0][$age_group]['frequency'])) {
                                                $tmp_data['None'][$video_id]['list'][0][$age_group]['frequency'] = 0;
                                            }
                                            if (!isset($tmp_data['None'][$video_id]['list'][0][$age_group]['duration'])) {
                                                $tmp_data['None'][$video_id]['list'][0][$age_group]['duration'] = 0;
                                            }
                                            $tmp_data['None'][$video_id]['list'][0][$age_group]['frequency'] += $num_frequency;
                                            $tmp_data['None'][$video_id]['list'][0][$age_group]['duration'] += $num_duration;
                                        }
                                    }
                                    //end
                                }
                            }
                        }
                    }
                }
            }
        }

        //sort the data.
        if (empty($tmp_data)) return array('data'=>array(),'video_ids'=>array());
        if (isset($data)) unset($data);
        $sort_name_desc1 = strtolower($sort_by);
        $sort_name_desc2 = (strtolower($sort_by)=='frequency') ? 'duration' : 'frequency';

        //intergration datas.
        $data = array();
        if (!empty($category_paths)) {
            foreach($category_paths as $cp=>$cp_name) {
                if (isset($tmp_data[$cp_name])) {
                    $key = $cp_name;
                    $value = $tmp_data[$key];
                    if (empty($value)) continue;
                    foreach($value as $k2=>$val2) {//video_id
                        if (empty($val2)) {
                            unset($tmp_data[$cp_name][$k2]);
                            continue;
                        }
                        foreach($val2 as $k3=>$val3) {//list
                            if (empty($val3)) continue;
                            $num_f = 0;
                            $num_d = 0;
                            $k4_flag = count($val3);
                            $k5_flag = false;
                            foreach($val3 as $k4=>$val4) {//gender
                                if (empty($val4)) {
                                    $k4_flag--;
                                    if (!in_array($k4,$genders)) unset($tmp_data[$cp_name][$k2][$k3][$k4]);
                                    continue;
                                }
                                foreach($val4 as $k5=>$val5) {//age_group
                                    if (empty($val5)) {
                                        $k5_flag = true;
                                        unset($tmp_data[$cp_name][$k2][$k3][$k4][$k5]);
                                        continue;
                                    }
                                    $num_f+=$val5[$sort_name_desc1];
                                    $num_d+=$val5[$sort_name_desc2];
                                }
                            }

                            //if the sub-value is null then continue.
                            if (!$k4_flag || $k5_flag) continue;
                            $data[$key][$k2][$sort_name_desc1] = $num_f;
                            $data[$key][$k2][$sort_name_desc2] = $num_d;
                            $data[$key][$k2]['video_id'] = $k2;
                            $tmp_data[$key][$k2]['attr'] = array('video_id'=>$k2,$sort_name_desc1=>$num_f,$sort_name_desc2=>$num_d);
                                
                        }
                    }
                }
            }
        }
        else {
            foreach($tmp_data as $key=>$value) {
                if (empty($value)) continue;
                foreach($value as $k2=>$val2) {//video_id
                    if (empty($val2)) {
                        unset($tmp_data[$key][$k2]);
                        continue;
                    }
                    foreach($val2 as $k3=>$val3) {//list
                        if (empty($val3)) continue;
                        $num_f = 0;
                        $num_d = 0;
                        $k4_flag = count($val3);
                        $k5_flag = false;
                        foreach($val3 as $k4=>$val4) {//gender
                            if (empty($val4)) {
                                $k4_flag--;
                                if (!in_array($k4,$genders)) unset($tmp_data[$key][$k2][$k3][$k4]);
                                continue;
                            }
                            foreach($val4 as $k5=>$val5) {//age_group
                                if (empty($val5)) {
                                    $k5_flag = true;
                                    unset($tmp_data[$key][$k2][$k3][$k4][$k5]);
                                    continue;
                                }
                                $num_f+=$val5[$sort_name_desc1];
                                $num_d+=$val5[$sort_name_desc2];
                            }
                        }

                        //if the sub-value is null then continue.
                        if (!$k4_flag || $k5_flag) continue;
                        $data[$key][$k2][$sort_name_desc1] = $num_f;
                        $data[$key][$k2][$sort_name_desc2] = $num_d;
                        $data[$key][$k2]['video_id'] = $k2;
                        $tmp_data[$key][$k2]['attr'] = array('video_id'=>$k2,$sort_name_desc1=>$num_f,$sort_name_desc2=>$num_d);
                            
                    }
                }
            }
        }
        
        //sort and get top 10 video id.
        $tmp_result = array();
        $all_videos = array();
        foreach($data as $key=>$value) {//category
            $tmp_data_ = array();
            $desc_data_first = array();
            $desc_data_second = array();
            $tmp_data_ = $value;
            
            foreach($tmp_data_ as $k=>$val) {
                $desc_data_first[$k] = $val[$sort_name_desc1];
                $desc_data_second[$k] = $val[$sort_name_desc2];
            }
            
            //sort the array.
            $sort_flag = array_multisort($desc_data_first,SORT_DESC,$desc_data_second,SORT_DESC,$tmp_data_);

            //get the top ten.
            $video_ids = array();
            if ($sort_flag) {
                $n = 1;
                foreach($tmp_data_ as $value) {
                    if ($n>$top_limit) break;
                    $video_id = $value['video_id'];
                    $video_ids[] = $video_id;
                    if (!in_array($video_id,$all_videos)) $all_videos[] = $video_id;
                    $n++;
                }
            }
            if (isset($tmp_data_)) unset($tmp_data_);       
            if (isset($desc_data_first)) unset($desc_data_first);
            if (isset($desc_data_second)) unset($desc_data_second);

            //get source data.
            if (!isset($tmp_result[$key])) $tmp_result[$key] = array();
            foreach($video_ids as $video_id) {
                $tmp_result[$key][$video_id] = array();
                $tmp_result[$key][$video_id] = $tmp_data[$key][$video_id];
            }
            if (isset($video_ids)) unset($video_ids);
        }
        
        if (isset($tmp_data)) unset($tmp_data);
        if (isset($data)) unset($data);
        if (isset($result)) unset($result);
        return array('data'=>$tmp_result,'video_ids'=>$all_videos);
    }

    #subscription plan
    public function getSubscriptionPlanGroupCountOptimization($data,$category_paths,$age_groups,$genders,$content_types,$package_ids,$country_codes,$sort_by,$pegging_countries_code,$top_limit) {
        if (empty($data)) return array();
        $result = array();
        $tmp_data = array();
        $tmp_result = array();

        foreach($data as $value) {
            $json_data_arr = array();
            $json_data_arr = json_decode($value['json_data'],true);
            
            foreach($json_data_arr as $category_path=>$category_val) {
                //if no exist then unset it.
                if (!empty($category_paths) && !in_array($category_path,$category_paths)) continue;
                if (!empty($category_paths) && !isset($tmp_data[$category_path])) $tmp_data[$category_path] = array();
                if (empty($category_paths) && !isset($tmp_data['None'])) $tmp_data['None'] = array();

                foreach($category_val as $video_id=>$video_val) {
                    //if no exist then unset it.
                    if (!empty($category_paths) && !isset($tmp_data[$category_path][$video_id])) {
                        $tmp_data[$category_path][$video_id] = array();
                        $tmp_data[$category_path][$video_id]['list'] = array();
                    }
                    elseif (!isset($tmp_data['None'][$video_id])) {
                        $tmp_data['None'][$video_id] = array();
                        $tmp_data['None'][$video_id]['list'] = array();
                    }

                    foreach($video_val as $country_code=>$country_val) {
                        //if no exist then unset it.
                        if (!empty($country_codes) && !in_array($country_code,$country_codes)) continue;
                        foreach($country_val as $plan_id=>$plan_val) {
                            if (!empty($package_ids) && !in_array($plan_id,$package_ids)) continue;
                            if ($content_types==2 && $plan_id==0) continue;
                            if ($content_types==1 && $plan_id!=0) continue;

                            if (!empty($category_paths) && (!isset($tmp_data[$category_path][$video_id]['list']['Premium'][$plan_id]))) {
                                $tmp_data[$category_path][$video_id]['list']['Premium'][$plan_id] = array();
                            }
                            elseif (!isset($tmp_data['None'][$video_id]['list']['Premium'][$plan_id])) {
                                $tmp_data['None'][$video_id]['list']['Premium'][$plan_id] = array();
                            }

                            $num_frequency = 0;
                            $num_duration = 0;
                            $tmp_flag1 = false;
                            $tmp_flag2 = false;
                            foreach($plan_val as $gender=>$gender_val) {
                                if (!empty($genders) && !in_array($gender,$genders)) {
                                    $tmp_flag1 = true;
                                    continue;
                                }
                                foreach($gender_val as $age=>$age_group_val) {
                                    if (!empty($age_groups) && !in_array($age,$age_groups)) {
                                        $tmp_flag2 = true;
                                        continue;
                                    }
                                    $num_frequency += (int)$age_group_val['frequency'];
                                    $num_duration += (int)$age_group_val['duration'];
                                }
                            }

                            if ($tmp_flag1 || $tmp_flag2) continue;

                            if (!empty($category_paths)) {
                                if (!isset($tmp_data[$category_path][$video_id]['list']['Premium'][$plan_id]['frequency'])) {
                                    $tmp_data[$category_path][$video_id]['list']['Premium'][$plan_id]['frequency'] = 0;
                                }
                                if (!isset($tmp_data[$category_path][$video_id]['list']['Premium'][$plan_id]['duration'])) {
                                    $tmp_data[$category_path][$video_id]['list']['Premium'][$plan_id]['duration'] = 0;
                                }
                                $tmp_data[$category_path][$video_id]['list']['Premium'][$plan_id]['frequency'] += $num_frequency;
                                $tmp_data[$category_path][$video_id]['list']['Premium'][$plan_id]['duration'] += $num_duration;
                            }
                            else {
                                if (!isset($tmp_data['None'][$video_id]['list']['Premium'][$plan_id]['frequency'])) {
                                    $tmp_data['None'][$video_id]['list']['Premium'][$plan_id]['frequency'] = 0;
                                }
                                if (!isset($tmp_data['None'][$video_id]['list']['Premium'][$plan_id]['duration'])) {
                                    $tmp_data['None'][$video_id]['list']['Premium'][$plan_id]['duration'] = 0;
                                }
                                $tmp_data['None'][$video_id]['list']['Premium'][$plan_id]['frequency'] += $num_frequency;
                                $tmp_data['None'][$video_id]['list']['Premium'][$plan_id]['duration'] += $num_duration;
                            }
                            
                            //end.
                        }

                    }
                }
            }
        }

        //sort the data.
        if (empty($tmp_data)) return array('data'=>array(),'video_ids'=>array());
        if (isset($data)) unset($data);

        $sort_name_desc1 = strtolower($sort_by);
        $sort_name_desc2 = (strtolower($sort_by)=='frequency') ? 'duration' : 'frequency';

        //intergration datas.
        $data = array();
        if (!empty($category_paths)) {
            foreach($category_paths as $cp=>$cp_name) {
                if (isset($tmp_data[$cp_name])) {
                    $key = $cp_name;
                    $value = $tmp_data[$key];
                    if (empty($value)) continue;
                    foreach($value as $k2=>$val2) {//video_id
                        if (empty($val2)) continue;
                        foreach($val2 as $k3=>$val3) {//list
                            if (empty($val3)) continue;
                            $num_f = 0;
                            $num_d = 0;
                            foreach($val3 as $k4=>$val4) {//premium
                                if (empty($val4)) continue;
                                $k5_num = count($val4);
                                foreach($val4 as $k5=>$val5) {//plan id
                                    if (empty($val5)) {
                                        $k5_num--;
                                        continue;
                                    }
                                    $num_f+=$val5[$sort_name_desc1];
                                    $num_d+=$val5[$sort_name_desc2];
                                }
                            }

                            if (!$k5_num) continue;
                            $data[$key][$k2][$sort_name_desc1] = $num_f;
                            $data[$key][$k2][$sort_name_desc2] = $num_d;
                            $data[$key][$k2]['video_id'] = $k2;
                            $tmp_data[$key][$k2]['attr'] = array('video_id'=>$k2,$sort_name_desc1=>$num_f,$sort_name_desc2=>$num_d);
                                
                        }
                    }
                }
            }
        }
        else {
            foreach($tmp_data as $key=>$value) {
                if (empty($value)) continue;
                foreach($value as $k2=>$val2) {//video_id
                    if (empty($val2)) continue;
                    foreach($val2 as $k3=>$val3) {//list
                        if (empty($val3)) continue;
                        $num_f = 0;
                        $num_d = 0;
                        foreach($val3 as $k4=>$val4) {//premium
                            if (empty($val4)) continue;
                            $k5_num = count($val4);
                            foreach($val4 as $k5=>$val5) {//plan id
                                if (empty($val5)) {
                                    $k5_num--;
                                    continue;
                                }
                                $num_f+=$val5[$sort_name_desc1];
                                $num_d+=$val5[$sort_name_desc2];
                            }
                        }

                        if (!$k5_num) continue;
                        $data[$key][$k2][$sort_name_desc1] = $num_f;
                        $data[$key][$k2][$sort_name_desc2] = $num_d;
                        $data[$key][$k2]['video_id'] = $k2;
                        $tmp_data[$key][$k2]['attr'] = array('video_id'=>$k2,$sort_name_desc1=>$num_f,$sort_name_desc2=>$num_d);
                            
                    }
                }
            }
        }

        //sort and get top 10 video id.
        $tmp_result = array();
        $all_videos = array();
        foreach($data as $key=>$value) {//category
            $tmp_data_ = array();
            $desc_data_first = array();
            $desc_data_second = array();
            $tmp_data_ = $value;
            
            foreach($tmp_data_ as $k=>$val) {
                $desc_data_first[$k] = $val[$sort_name_desc1];
                $desc_data_second[$k] = $val[$sort_name_desc2];
            }
            
            //sort the array.
            $sort_flag = array_multisort($desc_data_first,SORT_DESC,$desc_data_second,SORT_DESC,$tmp_data_);

            //get the top ten.
            $video_ids = array();
            if ($sort_flag) {
                $n = 1;
                foreach($tmp_data_ as $value) {
                    if ($n>$top_limit) break;
                    $video_id = $value['video_id'];
                    $video_ids[] = $video_id;
                    if (!in_array($video_id,$all_videos)) $all_videos[] = $video_id;
                    $n++;
                }
            }
            if (isset($tmp_data_)) unset($tmp_data_);       
            if (isset($desc_data_first)) unset($desc_data_first);
            if (isset($desc_data_second)) unset($desc_data_second);

            //get source data.
            if (!isset($tmp_result[$key])) $tmp_result[$key] = array();
            foreach($video_ids as $video_id) {
                $tmp_result[$key][$video_id] = array();
                $tmp_result[$key][$video_id] = $tmp_data[$key][$video_id];
            }
            if (isset($video_ids)) unset($video_ids);
        }
        
        if (isset($tmp_data)) unset($tmp_data);
        if (isset($data)) unset($data);
        if (isset($result)) unset($result);
        
        return array('data'=>$tmp_result,'video_ids'=>$all_videos);
    }

    #region&coutnry group.
    public function getRegionCountryGroupCountOptimization($data,$category_paths,$age_groups,$genders,$content_types,$package_ids,$country_codes,$sort_by,$pegging_countries_code,$top_limit) {
        if (empty($data)) return array();
        $result = array();
        $tmp_data = array();
        $tmp_result = array();

        foreach($data as $value) {
            $json_data_arr = array();
            $json_data_arr = json_decode($value['json_data'],true);

            foreach($json_data_arr as $category_path=>$category_val) {
                //if no exist then unset it.
                if (!empty($category_paths) && !in_array($category_path,$category_paths)) {
                    if (isset($tmp_data[$category_path])) unset($tmp_data[$category_path]);
                    if (isset($tmp_data['None'])) unset($tmp_data['None']);
                    continue;
                }
                if (!empty($category_paths) && !isset($tmp_data[$category_path])) {
                    $tmp_data[$category_path] = array();
                }
                elseif (empty($category_paths)) {
                    if (!isset($tmp_data['None'])) {
                        $tmp_data['None'] = array();
                    }
                }
                foreach($category_val as $video_id=>$video_val) {
                    //if no exist then unset it.
                    if (!empty($category_paths) && !isset($tmp_data[$category_path][$video_id])) {
                        $tmp_data[$category_path][$video_id] = array();
                        $tmp_data[$category_path][$video_id]['list'] = array();
                    }
                    else {
                        if (!isset($tmp_data['None'][$video_id])) {
                            $tmp_data['None'][$video_id] = array();
                            $tmp_data['None'][$video_id]['list'] = array();
                        }
                    }
                    foreach($video_val as $country_code=>$country_val) {
                        //if no exist then unset it.
                        if (!empty($country_codes) && !in_array($country_code,$country_codes)) {
                            //if (isset($tmp_data[$category_path][$video_id])) unset($tmp_data[$category_path][$video_id]);
                            //if (isset($tmp_data['None'][$video_id])) unset($tmp_data['None'][$video_id]);
                            //if (isset($tmp_data[$category_path])) unset($tmp_data[$category_path]);
                            continue;
                        }
                        
                        //get country's region & area
                        if (!isset($pegging_countries_code[$country_code])) continue;
                        $country_arr_ = $pegging_countries_code[$country_code];
                        $region = $country_arr_['Region'];
                        $area = $country_arr_['Area'];
                        if (!empty($category_paths)) {
                            if (!isset($tmp_data[$category_path][$video_id]['list'][$region][$area])) {
                                $tmp_data[$category_path][$video_id]['list'][$region][$area] = array();
                            }
                            if (!isset($tmp_data[$category_path][$video_id]['list'][$region][$area][$country_code])) {
                                $tmp_data[$category_path][$video_id]['list'][$region][$area][$country_code] = array();
                            }
                        }
                        else {
                            if (!isset($tmp_data['None'][$video_id]['list'][$region][$area])) {
                                $tmp_data['None'][$video_id]['list'][$region][$area] = array();
                            }
                            if (!isset($tmp_data['None'][$video_id]['list'][$region][$area][$country_code])) {
                                $tmp_data['None'][$video_id]['list'][$region][$area][$country_code] = array();
                            }
                        }

                        $num_frequency = 0;
                        $num_duration = 0;
                        $tmp_flag1 = $tmp_flag2 = $tmp_flag3 = false;
                        foreach($country_val as $plan_id=>$plan_val) {
                            if ($content_types==1 && $plan_id!=0) {
                                $tmp_flag1 = true;
                                continue;
                            }
                            if ((!empty($package_ids) && !in_array($plan_id,$package_ids)) || ($content_types==2 && $plan_id==0)) {
                                $tmp_flag1 = true;
                                continue;
                            }
                            foreach($plan_val as $gender=>$gender_val) {
                                if (!empty($genders) && !in_array($gender,$genders)) {
                                    $tmp_flag2 = true;
                                    continue;
                                }
                                foreach($gender_val as $age=> $age_group_val) {
                                    if (!empty($age_groups) && !in_array($age,$age_groups)) {
                                        $tmp_flag3 = true;
                                        continue;
                                    }
                                    $num_frequency += (int)$age_group_val['frequency'];
                                    $num_duration += (int)$age_group_val['duration'];
                                }
                            }
                        }

                        if ($tmp_flag1 || $tmp_flag2 || $tmp_flag3 ) continue;

                        //add total count.
                        if (!empty($category_paths)) {
                            if (!isset($tmp_data[$category_path][$video_id]['list'][$region][$area][$country_code]['frequency'])) {
                                $tmp_data[$category_path][$video_id]['list'][$region][$area][$country_code]['frequency'] = 0;
                            }
                            if (!isset($tmp_data[$category_path][$video_id]['list'][$region][$area][$country_code]['duration'])) {
                                $tmp_data[$category_path][$video_id]['list'][$region][$area][$country_code]['duration'] = 0;
                            }
                            $tmp_data[$category_path][$video_id]['list'][$region][$area][$country_code]['frequency'] += $num_frequency;
                            $tmp_data[$category_path][$video_id]['list'][$region][$area][$country_code]['duration'] += $num_duration;
                        }
                        else {
                            if (!isset($tmp_data['None'][$video_id]['list'][$region][$area][$country_code]['frequency'])) {
                                $tmp_data['None'][$video_id]['list'][$region][$area][$country_code]['frequency'] = 0;
                            }
                            if (!isset($tmp_data['None'][$video_id]['list'][$region][$area][$country_code]['duration'])) {
                                $tmp_data['None'][$video_id]['list'][$region][$area][$country_code]['duration'] = 0;
                            }
                            $tmp_data['None'][$video_id]['list'][$region][$area][$country_code]['frequency'] += $num_frequency;
                            $tmp_data['None'][$video_id]['list'][$region][$area][$country_code]['duration'] += $num_duration;
                        }
                    }
                }
            }
        }

        //sort the data.
        if (isset($data)) unset($data);

        $sort_name_desc1 = strtolower($sort_by);
        $sort_name_desc2 = (strtolower($sort_by)=='frequency') ? 'duration' : 'frequency';

        //intergration datas.
        $data = array();
        if (!empty($category_paths)) {
            foreach($category_paths as $cp=>$cp_name) {
                if (isset($tmp_data[$cp_name])) {
                    $key = $cp_name;
                    $value = $tmp_data[$key];
                    if (empty($value)) continue;
                    foreach($value as $k2=>$val2) {//video_id
                        if (empty($val2)) continue;
                        foreach($val2 as $k3=>$val3) {//list
                            if (empty($val3)) continue;
                            $num_f = 0;
                            $num_d = 0;
                            $tmp_flag1 = count($val3);
                            foreach($val3 as $k4=>$val4) {//region-Asia
                                if (empty($val4)) {
                                    $tmp_flag1--;
                                    continue;
                                }
                                $tmp_flag2 = count($val4);
                                foreach($val4 as $k5=>$val5) {//area-Asia
                                    if (empty($val5)) {
                                        $tmp_flag2--;
                                        continue;
                                    }
                                    $tmp_flag3 = count($val5);
                                    foreach($val5 as $k6=>$val6) {
                                        if (empty($val6)) {
                                            $tmp_flag3--;
                                            continue;
                                        }
                                        $num_f+=$val6[$sort_name_desc1];
                                        $num_d+=$val6[$sort_name_desc2];
                                    }
                                }
                            }
                            if (!$tmp_flag1 || !$tmp_flag2 || !$tmp_flag3) continue;
                            $data[$key][$k2][$sort_name_desc1] = $num_f;
                            $data[$key][$k2][$sort_name_desc2] = $num_d;
                            $data[$key][$k2]['video_id'] = $k2;
                            $tmp_data[$key][$k2]['attr'] = array('video_id'=>$k2,$sort_name_desc1=>$num_f,$sort_name_desc2=>$num_d);
                                
                        }
                    }
                }
            }
        }
        else {//None.
            foreach($tmp_data as $key=>$value) {//category
                if (empty($value)) continue;
                foreach($value as $k2=>$val2) {//video_id
                    if (empty($val2)) continue;
                    foreach($val2 as $k3=>$val3) {//list
                        if (empty($val3)) continue;
                        $num_f = 0;
                        $num_d = 0;
                        $tmp_flag1 = count($val3);
                        foreach($val3 as $k4=>$val4) {//region-Asia
                            if (empty($val4)) {
                                $tmp_flag1--;
                                continue;
                            }
                            $tmp_flag2 = count($val4);
                            foreach($val4 as $k5=>$val5) {//area-Asia
                                if (empty($val5)) {
                                    $tmp_flag2--;
                                    continue;
                                }
                                $tmp_flag3 = count($val5);
                                foreach($val5 as $k6=>$val6) {
                                    if (empty($val6)) {
                                        $tmp_flag3--;
                                        continue;
                                    }
                                    $num_f+=$val6[$sort_name_desc1];
                                    $num_d+=$val6[$sort_name_desc2];
                                }
                            }
                        }
                        if (!$tmp_flag1 || !$tmp_flag2 || !$tmp_flag3) continue;
                        $data[$key][$k2][$sort_name_desc1] = $num_f;
                        $data[$key][$k2][$sort_name_desc2] = $num_d;
                        $data[$key][$k2]['video_id'] = $k2;
                        $tmp_data[$key][$k2]['attr'] = array('video_id'=>$k2,$sort_name_desc1=>$num_f,$sort_name_desc2=>$num_d);
                            
                    }
                }
            }
        }

        //sort and get top 10 video id.
        $tmp_result = array();
        $all_videos = array();
        foreach($data as $key=>$value) {//category
            $tmp_data_ = array();
            $desc_data_first = array();
            $desc_data_second = array();
            $tmp_data_ = $value;
            
            foreach($tmp_data_ as $k=>$val) {
                $desc_data_first[$k] = $val[$sort_name_desc1];
                $desc_data_second[$k] = $val[$sort_name_desc2];
            }
            
            //sort the array.
            $sort_flag = array_multisort($desc_data_first,SORT_DESC,$desc_data_second,SORT_DESC,$tmp_data_);

            //get the top ten.
            $video_ids = array();
            if ($sort_flag) {
                $n = 1;
                foreach($tmp_data_ as $value) {
                    if ($n>$top_limit) break;
                    $video_id = $value['video_id'];
                    $video_ids[] = $video_id;
                    if (!in_array($video_id,$all_videos)) $all_videos[] = $video_id;
                    $n++;
                }
            }
            if (isset($tmp_data_)) unset($tmp_data_);       
            if (isset($desc_data_first)) unset($desc_data_first);
            if (isset($desc_data_second)) unset($desc_data_second);

            //get source data.
            if (!isset($tmp_result[$key])) $tmp_result[$key] = array();
            foreach($video_ids as $video_id) {
                $tmp_result[$key][$video_id] = array();
                $tmp_result[$key][$video_id] = $tmp_data[$key][$video_id];
            }
            if (isset($video_ids)) unset($video_ids);
        }
        
        if (isset($tmp_data)) unset($tmp_data);
        if (isset($data)) unset($data);
        if (isset($result)) unset($result);

        return array('data'=>$tmp_result,'video_ids'=>$all_videos);
    }

    //2.video bitrate report.
    public function videoBitrateOfCountryOptimization($country_code,$condition) {
        $data = array();
        $result = array();
        $table_name = $this->getDayTableNameOfVideoBitrate();

        $query = "SELECT day_time,json_data ".
                 " FROM ".$table_name.
                 " WHERE 1 ".$condition.
                 " ORDER BY day_time ASC ";
        $data = Model::selectBySql($query);
        
        //choose the countries.
        if (!empty($country_code)) {
            $tmp_data = array();
            $member_data = array();
            $country_data = array();

            foreach($data as $key=>$value) {
                $json_data_arr = array();
                $json_data_arr = json_decode($value['json_data'],true);
                if (empty($json_data_arr)) continue;
                $data_code_list = array_keys($json_data_arr);
                
                foreach($country_code as $region=>$areas) {
                    if (!isset($country_data[$region])) {
                        $country_data[$region] = array();
                    }
                    foreach($areas as $area=>$countries) {
                        foreach($countries as $code=>$name) {
                            if (!isset($country_data[$region][$code])) {
                                $country_data[$region][$code] = array();
                            }

                            if (in_array($code,$data_code_list)) {
                                $real_data = $json_data_arr[$code];
                                foreach($real_data as $bitrate_k=>$bitrate_v) {
                                    if (!isset($country_data[$region][$code][$bitrate_k])) {
                                        $country_data[$region][$code][$bitrate_k] = 0;
                                    }

                                    $country_data[$region][$code][$bitrate_k] += $bitrate_v;
                                }
                            }
                        }
                    }
                }
                
                if(isset($json_data_arr)) unset($json_data_arr);
            }


            /**
            if (isset($data)) unset($data);
            //count the datas.                        
            foreach($country_data as $key=>$value) {
                foreach($value as $code=>$name) {
                    if (empty($name)) {
                        $tmp_data[$key][$code] = array();
                        continue;
                    }
                    foreach($name as $bitrate=>$val) {
                        $tmp_data[$key][$code][$bitrate] = count($val);
                    }
                }
            }
            $result = $tmp_data;
            if (isset($country_data)) unset($country_data);
            */
            $result = $country_data;
            if (isset($country_data)) unset($country_data);
        }
        else {
            //unchoose the country and get the total summary data
            $tmp_data = array();
            $member_data = array();
            foreach($data as $key=>$value) {
                $json_data_arr = array();
                $json_data_arr = json_decode($value['json_data'],true);
                if (empty($json_data_arr)) continue;
                foreach($json_data_arr as $country_code=>$country_val) {
                    /**
                    foreach($country_val as $member=>$member_val) {
                        $bitrate = $member_val['bitrate'];
                        $member_data[$bitrate][$member] = true;
                    }
                    */
                    foreach($country_val as $bitrate_k=>$bitrate_v) {
                        if (!isset($member_data[$bitrate_k])) {
                            $member_data[$bitrate_k] = 0 ;
                        }
                        $member_data[$bitrate_k] += $bitrate_v;
                    }
               }
            }
            
            /**
            if (isset($data)) unset($data);
            foreach($member_data as $key=>$value) {
                $tmp_data[$key] = count($value);
            }
            $result['summary'] = $tmp_data;
            */
            $result['summary'] = $member_data;
            if (isset($member_data)) unset($member_data);
        }

        return $result;
        
    }
    
    
    //3.OS And Browser report.
    public function OSAndBrowserOfCountryOptimization($country_code,$condition,$pegging_countries_code=null,$combination) {
        $data = array();
        $result = array();
        $table_name = $this->getDayTableNameOfOSAndBrowser();

        $query = "SELECT day_time,json_data ".
                 " FROM ".$table_name.
                 " WHERE 1 ".$condition.
                 " ORDER BY day_time ASC ";
        $data = Model::selectBySql($query);

        //choose the cuntries.
        if (!empty($country_code)) {
            if (empty($combination)) {
                $bo_data = array();
                foreach($data as $key=>$value) {
                    $json_data_arr = array();
                    $json_data_arr = json_decode($value['json_data'],true);
                    if (empty($json_data_arr)) continue;
                    foreach($json_data_arr as $c_code=>$c_val) {
                        $c_region = (isset($pegging_countries_code[$c_code]['Region'])) ? $pegging_countries_code[$c_code]['Region'] : null;
                        if (empty($c_region)) continue;
                        if (!isset($bo_data[$c_region])) {
                            $bo_data[$c_region] = array();
                        }
                        if (!isset($bo_data[$c_region][$c_code])) {
                            $bo_data[$c_region][$c_code] = array();
                        }
                        foreach($c_val as $bo_k=>$bo_v) {
                            if (!isset($bo_data[$c_region][$c_code][$bo_k])) $bo_data[$c_region][$c_code][$bo_k] = array();
                            foreach($bo_v as $bo_k2=>$bo_v2) {
                                if (!isset($bo_data[$c_region][$c_code][$bo_k][$bo_k2])) $bo_data[$c_region][$c_code][$bo_k][$bo_k2] = 0;
                                $total_n = ($bo_k=='os') ? $bo_v2['total'] : $bo_v2;
                                $bo_data[$c_region][$c_code][$bo_k][$bo_k2] += $total_n;
                            }
                        }
                    }
                    if (isset($json_data_arr)) unset($json_data_arr);
                }
            }
            else {
                $bo_data = array();
                foreach($data as $key=>$value) {
                    $json_data_arr = array();
                    $json_data_arr = json_decode($value['json_data'],true);
                    if (empty($json_data_arr)) continue;
                    foreach($json_data_arr as $c_code=>$c_val) {
                        $c_region = (isset($pegging_countries_code[$c_code]['Region'])) ? $pegging_countries_code[$c_code]['Region'] : null;
                        if (empty($c_region)) continue;
                        if (!isset($bo_data[$c_region])) {
                            $bo_data[$c_region] = array();
                        }
                        if (!isset($bo_data[$c_region][$c_code])) {
                            $bo_data[$c_region][$c_code] = array();
                        }
                        //setting country value.
                        foreach($c_val as $bo_k=>$bo_v) {
                            if ($bo_k != 'os') continue;
                            foreach($bo_v as $bo_k2=>$bo_v2) {
                                if (!isset($bo_data[$c_region][$c_code][$bo_k2])) $bo_data[$c_region][$c_code][$bo_k2] = array();
                                foreach((array)$bo_v2['item'] as $bo_k3=>$bo_v3) {
                                    if (!isset($bo_data[$c_region][$c_code][$bo_k2][$bo_k3])) {
                                        $bo_data[$c_region][$c_code][$bo_k2][$bo_k3] = 0;
                                    }
                                    $bo_data[$c_region][$c_code][$bo_k2][$bo_k3] += $bo_v3;
                                }
                            }
                        }                        
                    }
                    //unset
                    if (isset($json_data_arr)) unset($json_data_arr);
                }
            }
            if (isset($country_code)) unset($country_code);
            if (isset($combination)) unset($combination);
            if (isset($data)) unset($data);
            return $bo_data;
        }
        else {
            //pie chart by total summary.
            if (empty($combination)) {
                $tmp_data = array();
                $bo_data = array();
                foreach($data as $key=>$value) {
                    $json_data_arr = array();
                    $json_data_arr = json_decode($value['json_data'],true);
                    if (empty($json_data_arr)) continue;
                    foreach($json_data_arr as $country_code=>$country_val) {
                        foreach($country_val as $bo_k=>$bo_v) {
                            if (!isset($bo_data[$bo_k])) $bo_data[$bo_k] = array();
                            foreach($bo_v as $bo_k2=>$bo_v2) {
                                if (!isset($bo_data[$bo_k][$bo_k2])) $bo_data[$bo_k][$bo_k2] = 0;
                                $total_n = ($bo_k=='os') ? $bo_v2['total'] : $bo_v2;
                                $bo_data[$bo_k][$bo_k2] += $total_n;
                            }
                        }
                    }
                }
                if (isset($data)) unset($data);
                if (isset($tmp_data)) unset($tmp_data);
                $result['summary'] = $bo_data;
                if (isset($bo_data)) unset($bo_data);
            }
            else {
                //table line by total summary.
                $bo_data = array();
                foreach($data as $key=>$value) {
                    $json_data_arr = array();
                    $json_data_arr = json_decode($value['json_data'],true);
                    if (empty($json_data_arr)) continue;
                    foreach($json_data_arr as $country_code=>$country_val) {
                        foreach($country_val as $bo_k=>$bo_v) {
                            if ($bo_k != 'os') continue;
                            if (!isset($bo_data[$bo_k])) $bo_data[$bo_k] = array();
                            foreach($bo_v as $bo_k2=>$bo_v2) {
                                if (!isset($bo_data[$bo_k][$bo_k2])) $bo_data[$bo_k][$bo_k2] = array();
                                foreach((array)$bo_v2['item'] as $bo_k3=>$bo_v3) {
                                    if (!isset($bo_data[$bo_k][$bo_k2][$bo_k3])) $bo_data[$bo_k][$bo_k2][$bo_k3] = 0;
                                    $bo_data[$bo_k][$bo_k2][$bo_k3] += $bo_v3;
                                }
                            }
                        }
                    }
                }
                if (isset($data)) unset($data);
                $result['summary'] = $bo_data;
                if (isset($bo_data)) unset($bo_data);
            }
            return $result;
        }
    }


    //mstat inline old membership mstat data.
    public function getRegActiveConfirmData($date_by,$date_times) {
        $table_name = $this->getDailyMemberLog();
        $my_type = AC::$daily_format_type;
        $my_page_type = array('register','confirm','active','expired_confirm','expired');
        $result = array();
        switch($date_by) {
            case 'month':
                foreach($my_page_type as $type) {
                    if (!isset($result[$type])) $result[$type] = array();
                    $query = "SELECT SUBSTRING(day_time,1,7) AS YearMonth,sum(day_count) as MonthCount FROM ".$table_name.
                             " WHERE SUBSTRING(day_time,1,4)='".$date_times."' AND type=".$my_type[$type].
                             " GROUP BY SUBSTRING(day_time,1,7) ";
                    //echo $query."<br>";
                    $data = Model::selectBySql($query);
                    if (is_array($data)) {
                        foreach($data as $key=>$val) {
                            $date_time = $val['YearMonth'];
                            $num = $val['MonthCount'];
                            $result[$type][$date_time] = $num;
                        }
                    }
                }
                return $result;
                break;
            case 'date':
                foreach($my_page_type as $type) {
                    if (!isset($result[$type])) $result[$type] = array();
                    $query = "SELECT day_time,sum(day_count) as DateCount FROM ".$table_name.
                             " WHERE SUBSTRING(day_time,1,7)='".$date_times."' AND type=".$my_type[$type].
                             " GROUP BY day_time ";
                    $data = Model::selectBySql($query);
                    if (is_array($result)) {
                        foreach($data as $key=>$val) {
                            $date_time = $val['day_time'];
                            $num = $val['DateCount'];
                            $result[$type][$date_time] = $num;
                        }
                    }
                }
                return $result;
                break;
            case 'hour':
                foreach($my_page_type as $type) {
                    if (!isset($result[$type])) $result[$type] = array();
                    $query = "SELECT hour1,hour2,hour3,hour4,hour5,hour6,hour7,hour8,hour9,hour10,hour11,hour12,hour13,hour14,hour15,hour16,hour17,hour18,hour19,hour20,hour21,hour22,hour23 FROM ".$table_name.
                             " WHERE day_time='".$date_times."' AND type=".$my_type[$type];
                    $data = Model::selectBySql($query);
                    foreach($data as $key=>$val) {
                        $day_time = $date_times;
                        foreach($val as $hour_key=>$hour_val) {
                            $tmp_hour = substr($hour_key,4);
                            $tmp_hour = ($tmp_hour<10) ? '0'.$tmp_hour : $tmp_hour;
                            $tmp_key = $day_time.' '.$tmp_hour;
                            $result[$type][$tmp_key] = $hour_val;
                        }
                    }
                }
                return $result;
                break;
        }
    }

    public function getMemberLoginOutData($date_by,$date_times) {
        $table_name = $this->getDailyMemberLog();
        $my_type = AC::$daily_format_type;
        $my_page_type = array('login','logout');
        $result = array();
        switch($date_by) {
            case 'month':
                foreach($my_page_type as $type) {
                    if (!isset($result[$type])) $result[$type] = array();
                    $query = "SELECT SUBSTRING(day_time,1,7) AS YearMonth,sum(day_count) as MonthCount FROM ".$table_name.
                             " WHERE SUBSTRING(day_time,1,4)='".$date_times."' AND type=".$my_type[$type].
                             " GROUP BY SUBSTRING(day_time,1,7) ";
                    //echo $query."<br>";
                    $data = Model::selectBySql($query);
                    if (is_array($data)) {
                        foreach($data as $key=>$val) {
                            $date_time = $val['YearMonth'];
                            $num = $val['MonthCount'];
                            $result[$type][$date_time] = $num;
                        }
                    }
                }
                return $result;
                break;
            case 'date':
                foreach($my_page_type as $type) {
                    if (!isset($result[$type])) $result[$type] = array();
                    $query = "SELECT day_time,sum(day_count) as DateCount FROM ".$table_name.
                             " WHERE SUBSTRING(day_time,1,7)='".$date_times."' AND type=".$my_type[$type].
                             " GROUP BY day_time ";
                    $data = Model::selectBySql($query);
                    if (is_array($result)) {
                        foreach($data as $key=>$val) {
                            $date_time = $val['day_time'];
                            $num = $val['DateCount'];
                            $result[$type][$date_time] = $num;
                        }
                    }
                }
                return $result;
                break;
            case 'hour':
                foreach($my_page_type as $type) {
                    if (!isset($result[$type])) $result[$type] = array();
                    $query = "SELECT hour1,hour2,hour3,hour4,hour5,hour6,hour7,hour8,hour9,hour10,hour11,hour12,hour13,hour14,hour15,hour16,hour17,hour18,hour19,hour20,hour21,hour22,hour23 FROM ".$table_name.
                             " WHERE day_time='".$date_times."' AND type=".$my_type[$type];
                    $data = Model::selectBySql($query);
                    foreach($data as $key=>$val) {
                        $day_time = $date_times;
                        foreach($val as $hour_key=>$hour_val) {
                            $tmp_hour = substr($hour_key,4);
                            $tmp_hour = ($tmp_hour<10) ? '0'.$tmp_hour : $tmp_hour;
                            $tmp_key = $day_time.' '.$tmp_hour;
                            $result[$type][$tmp_key] = $hour_val;
                        }
                    }
                }
                return $result;
                break;
        }    
    }

    public function getForgetPasswordData($date_by,$date_times) {
        $data['forget'] = array();
        $table_name = $this->getMstatInlineMembership();
        switch($date_by) {
            case 'month':
                $query = "SELECT SUBSTRING(day_time,1,7) as YearMonth,SUM(data) as MonthCount FROM ".$table_name.
                         " WHERE type='forgot' AND SUBSTRING(day_time,1,4)='".$date_times."' ".
                         " GROUP BY SUBSTRING(day_time,1,7) ";
                $result = Model::selectBySql($query);
                foreach($result as $key=>$val) {
                    $date_time = $val['YearMonth'];
                    $num = $val['MonthCount'];
                    $data['forget'][$date_time] = $num;
                }
                return $data;
                break;
            case 'date':
                $query = "SELECT day_time,data as DateCount FROM ".$table_name.
                         " WHERE SUBSTRING(day_time,1,7)='".$date_times."' AND type='forgot' ".
                         " GROUP BY day_time ";
                $result = Model::selectBySql($query);
                foreach($result as $key=>$val) {
                    $date_time = $val['day_time'];
                    $num = $val['DateCount'];
                    $data['forget'][$date_time] = $num;                    
                }
                return $data;
                break;
       }
    }
    
    public function getEditProfileData($date_by,$date_times,$count_flag) {
        $data['edit_profile'] = array();
        $table_name = $this->getMstatInlineMembership();
        $type = ($count_flag==0) ? " AND (type='editprofile' OR type='detailinput' ) " : (($count_flag==1) ? " AND type='detailinput' " : null);
        switch($date_by) {
            case 'month':
                $query = "SELECT SUBSTRING(day_time,1,7) as YearMonth,SUM(data) as MonthCount FROM ".$table_name.
                         " WHERE SUBSTRING(day_time,1,4)='".$date_times."' ".$type.
                         " GROUP BY SUBSTRING(day_time,1,7) ";
                $result = Model::selectBySql($query);
                foreach($result as $key=>$val) {
                    $date_time = $val['YearMonth'];
                    $num = $val['MonthCount'];
                    $data['edit_profile'][$date_time] = $num;
                }
                return $data;
                break;
            case 'date':
                $query = "SELECT day_time,data as DateCount FROM ".$table_name.
                         " WHERE SUBSTRING(day_time,1,7)='".$date_times."' ".$type.
                         " GROUP BY day_time ";
                $result = Model::selectBySql($query);
                foreach($result as $key=>$val) {
                    $date_time = $val['day_time'];
                    $num = $val['DateCount'];
                    $data['edit_profile'][$date_time] = $num;                    
                }
                return $data;
                break;
       }
    }

    public function getMemberStatData($date_by,$date_times) {
        $data = array();
        $table_name = $this->getMstatInlineMembership();
        switch($date_by) {
            case 'month':
            case 'date':
            default:
                $query = "SELECT data FROM ".$table_name.
                         " WHERE type='oldmember' ".
                         " ORDER BY day_time DESC LIMIT 1";
                $result = Model::selectBySql($query);
                $result = json_decode($result[0]['data']);
                foreach($result as $key=>$val) {
                    $data[$key] = $val;
                }
                return $data;
                break;
        }
    }

    //Hot Search Keyword data.
    public function getHotSearchKeywordData($date_by,$date_times,$product,$limit) {
        $config = AC::REPORT_TOOL_HOTSEARCH_CONFIG_FILE;
        Model::$dbo['r'] = CommonOtherUtils::getDbConn($config,1);
        $daily_table_name = $this->getMstatInlineHotSearchDaily();

        //get all keyword of product total hits.
        $all_keywords = array();
        $query = "SELECT sum(day_count) AS Hits,keyword,product FROM ".$daily_table_name.
                 " GROUP BY keyword,product ";
        $all_data = Model::selectBySql($query);
        foreach($all_data as $key=>$val) {
            $key_word = md5($val['keyword']);
            $product_ = $val['product'];
            $all_keywords[$product_][$key_word] = array('hits'=>$val['Hits'],'product'=>$product_,'keyword'=>$val['keyword']);
        }

        $condition = null;
        $tmp_search = null;
        $group_by = null;
        $product_flag = null;
        
        if ($product!='all') {
            $condition .= " AND product='".$product."'";
            $tmp_search = $condition;
            $group_by = ",product";
        }
        else {
            $product_flag = 'All';
        }

        switch($date_by) {
            case 'month':
                $condition .= " AND SUBSTRING(day_time,1,4)='".$date_times."'";                
                break;
            case 'date':
                $condition .= " AND SUBSTRING(day_time,1,7)='".$date_times."'";
                break;
            case 'hour':
                $condition .= " AND SUBSTRING(day_time,1,10)='".$date_times."'";
                break;
        }


        $query = "SELECT sum(day_count) AS Total,keyword".$group_by." FROM ".$daily_table_name.
                 " WHERE 1=1 ".$condition.
                 " GROUP BY keyword".$group_by.
                 " ORDER BY Total DESC ".
                 " LIMIT 0,".$limit;

        $daily_data = Model::selectBySql($query);
        $search_arr = array();
        $result = array();
        $num = 1;
        foreach($daily_data as $key=>$val) {
            $keyword = $val['keyword'];
            $total = $val['Total'];
            $products = ($product!='all') ? $val['product'] : 'All';
            if (!isset($search_arr[$products])) $search_arr[$products] = array();
            $md5_key = md5($keyword);
            if (!isset($search_arr[$products][$md5_key])) $search_arr[$products][$md5_key]=0;
            $search_arr[$products][$md5_key] = array('total'=>$total,'num'=>$num,'keyword'=>$keyword);
            $num++;
        }
        if (isset($daily_data)) unset($daily_data);
        
        if ($product_flag=='All') {//all
            if (!isset($search_arr['All'])) return $result;
            foreach($search_arr['All'] as $key=>$total) {
                $all_total_ = 0;
                foreach($all_keywords as $product=>$val) {
                    $p_keys = array_keys($val);
                    if (in_array($key,$p_keys)) {
                        $all_total_ += $val[$key]['hits'];
                    }
                }

                $num_ = $total['num'];
                $daily_total_ = $total['total'];
                $key_word = $total['keyword'];
                $result[$num_] = array('keyword'=>$key_word,'daily'=>$daily_total_,'history'=>$all_total_);
            }
        }
        else {

            foreach($search_arr as $prd=>$val) {
                foreach($val as $keyword=>$val2) {
                    $all_total_ = (isset($all_keywords[$prd][$keyword])) ? $all_keywords[$prd][$keyword]['hits'] : 0;
                    $num_ = $val2['num'];
                    $daily_total_ = $val2['total'];
                    $key_word = $val2['keyword'];
                    $result[$num_] = array('keyword'=>$key_word,'daily'=>$daily_total_,'history'=>$all_total_);
                }
            }
        }
        
        return $result;             
    }


}

?>